from bs4 import BeautifulSoup
from bs4 import UnicodeDammit
import urllib.request
import sqlite3

# 城市和其对应的天气页面URL
cities = {
    "北京": "https://www.weather.com.cn/weather/101010100.shtml",
    "上海": "https://www.weather.com.cn/weather/101020100.shtml",
    "广州": "https://www.weather.com.cn/weather/101280101.shtml",
    "杭州": "https://www.weather.com.cn/weather/101210101.shtml"
}

# 数据库连接和表格创建
conn = sqlite3.connect('cities_weather.db')
cursor = conn.cursor()
cursor.execute('''  
   CREATE TABLE IF NOT EXISTS weather (  
       序号 INTEGER PRIMARY KEY AUTOINCREMENT,  -- 让数据库自动生成唯一序号
       地区 varchar(16),
       日期 varchar(16),  
       天气信息 varchar(64),  
       温度 varchar(32)
   )  
''')

# 遍历每个城市及其URL，爬取天气信息
for city, url in cities.items():
    try:
        headers = {"User-Agent": "Mozilla/5.0(Windows;U;Windows NT 6.0 x64;en-US;rv:1.9pre)Gecko/2008072421 Minefield/3.0.2pre"}
        req = urllib.request.Request(url, headers=headers)
        data = urllib.request.urlopen(req).read()
        dammit = UnicodeDammit(data, ["utf-8", "gbk"])
        data = dammit.unicode_markup

        # 使用 BeautifulSoup 解析网页内容
        soup = BeautifulSoup(data, "lxml")
        lis = soup.select("ul[class='t clearfix'] li")

        # 遍历每一天天气数据
        for li in lis:
            try:
                date = li.select("h1")[0].text
                weather = li.select("p[class='wea']")[0].text
                tem = li.select("p[class='tem']")[0].text.strip()  # 获取温度，去除空白

                # 检查数据库中是否已存在该地区和日期的记录
                cursor.execute('SELECT COUNT(*) FROM weather WHERE 地区 = ? AND 日期 = ?', (city, date))
                exists = cursor.fetchone()[0]

                if exists == 0:
                    # 插入数据到数据库
                    cursor.execute('''  
                        INSERT INTO weather (地区, 日期, 天气信息, 温度) VALUES (?, ?, ?, ?)  
                    ''', (city, date, weather, tem))

            except Exception as e:
                print(f"处理 {city} 的天气信息时出错: {e}")

        # 提交城市的数据
        conn.commit()

    except Exception as e:
        print(f"爬取 {city} 的天气信息时出错: {e}")

# 查询并打印数据库中的所有城市数据以验证插入是否成功
cursor.execute('SELECT * FROM weather')
rows = cursor.fetchall()

# 打印表头
print("序号 | 地区 | 日期    | 天气信息  | 温度")
print("-" * 50)  # 打印分隔线
for row in rows:
    print(f"{row[0]:<5} | {row[1]:<5} | {row[2]:<10} | {row[3]:<10} | {row[4]:<10}")

# 关闭数据库连接
conn.close()
